﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace Dapper_Core.Common
{
    public class ExcelUtility
    {
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="fileName">文件完整路径名</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
        {
            //定义要返回的datatable对象
            DataTable data = new DataTable();
            //excel工作表
            ISheet sheet = null;
            //数据开始行(排除标题行)
            int startRow = 0;
            try
            {
                if (!File.Exists(fileName))
                {
                    return null;
                }
                //根据指定路径读取文件
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                //根据文件流创建excel数据结构
                IWorkbook workbook = WorkbookFactory.Create(fs);
                //IWorkbook workbook = new HSSFWorkbook(fs);
                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    //如果没有指定的sheetName，则尝试获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果第一行是标题列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null
                        if (!RowIsValid(row)) continue; //若本行是空行则直接进行下一次循环

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 将文件流读取到DataTable数据表中
        /// </summary>
        /// <param name="fileStream">文件流</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
        {
            //定义要返回的datatable对象
            DataTable data = new DataTable();
            //excel工作表
            ISheet sheet = null;
            //数据开始行(排除标题行)
            int startRow = 0;
            try
            {
                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                IWorkbook workbook = WorkbookFactory.Create(fileStream);
                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    //如果没有指定的sheetName，则尝试获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果第一行是标题列名
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null　　　　　　　
                        if (!RowIsValid(row)) continue; //若本行是空行则直接进行下一次循环

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            //同理，没有数据的单元格都默认是null
                            ICell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    //判断是否日期类型
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        dataRow[j] = row.GetCell(j).DateCellValue;
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).ToString().Trim();
                                    }
                                }
                                else
                                {
                                    dataRow[j] = row.GetCell(j).ToString().Trim();
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 判断Excel中的行是否为空行
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public static bool RowIsValid(IRow row)
        {
            bool flag = false;
            for (int i = 0; i < row.LastCellNum; i++)
            {
                if (row.GetCell(i) != null)
                {
                    if (!string.IsNullOrEmpty(row.GetCell(i).ToString()))
                    {
                        flag = true;
                        break;
                    }
                }
            }
            return flag ? true : false;
        }

        /// <summary>
        /// 获取XSSF字体
        /// </summary>
        /// <param name="book"></param>
        /// <param name="fontName"></param>
        /// <returns></returns>
        private static XSSFFont GetXSSFFont(XSSFWorkbook book, string fontName, short color = HSSFColor.Black.Index)
        {
            XSSFFont font = null;
            font = (XSSFFont)book.CreateFont();
            switch (fontName)
            {
                case "Song_Bold":
                    font.FontName = "宋体";
                    font.FontHeightInPoints = 11;
                    font.IsBold = true;
                    font.Color = color;
                    break;
                case "Song":
                    font.FontName = "宋体";
                    font.FontHeightInPoints = 11;
                    font.Color = color;
                    break;
            }

            return font;
        }

        /// <summary>
        /// 获取XSSF样式
        /// </summary>
        /// <param name="styleName"></param>
        /// <returns></returns>
        private static XSSFCellStyle GetXSSFStyle(XSSFWorkbook book, XSSFFont font, string styleName)
        {
            XSSFCellStyle style = null;
            style = (XSSFCellStyle)book.CreateCellStyle();
            switch (styleName)
            {
                case "Simple":
                    style.SetFont(font);
                    break;
                case "Yellow":
                    style.SetFont(font);
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                    style.FillPattern = FillPattern.SolidForeground;
                    style.Alignment = HorizontalAlignment.Center;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
                case "Yellow_Double":
                    style.SetFont(font);
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                    style.FillPattern = FillPattern.SolidForeground;
                    style.Alignment = HorizontalAlignment.Center;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Double;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Double;
                    break;
                case "Grey":
                    style.SetFont(font);
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
                    style.FillPattern = FillPattern.SolidForeground;
                    style.Alignment = HorizontalAlignment.Center;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
                case "Green":
                    style.SetFont(font);
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
                    style.FillPattern = FillPattern.SolidForeground;
                    style.Alignment = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;// 垂直居中
                    style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
                case "Green_Double":
                    style.SetFont(font);
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
                    style.FillPattern = FillPattern.SolidForeground;
                    style.Alignment = HorizontalAlignment.Center;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Double;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Double;
                    break;
            }
            return style;
        }

        /// <summary>
        /// 创建单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="columnIndex"></param>
        /// <param name="cellValue"></param>
        /// <param name="cellStyle"></param>
        private static void CreateCell(IRow row, int columnIndex, string cellValue, XSSFCellStyle cellStyle = null)
        {
            XSSFCell cell = (XSSFCell)row.CreateCell(columnIndex);
            if (!string.IsNullOrEmpty(cellValue))
            {
                cell.SetCellValue(cellValue);
            }
            if (cellStyle != null)
            {
                cell.CellStyle = cellStyle;
            }
        }

        /// <summary>
        /// 创建合并的单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="firstRow"></param>
        /// <param name="lastRow"></param>
        /// <param name="firstCol"></param>
        /// <param name="lastCol"></param>
        private static void CreateCellRange(XSSFWorkbook book, ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 设置单元格宽
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columnIndex"></param>
        /// <param name="width"></param>
        private static void SetColumnWidth(ISheet sheet, int columnIndex, int width)
        {
            sheet.SetColumnWidth(columnIndex, width * 256);
        }
    }
}
